package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Dept;
import model.Teacher;
import util.DBUtil;
import util.Page;
import dao.DeptDAO;

public class DeptDAOImpl implements DeptDAO {

	private DBUtil dbu = new DBUtil();
	private Connection conn = null;
	private PreparedStatement pstat = null;
	private ResultSet rs = null;

	public Dept getDeptHeader(int deptid) {
		{
			Dept dept = new Dept();
			ResultSet rss = null;
			conn = dbu.getConnection();
			try {
				pstat = conn.prepareStatement("select * from dept where id="
						+ deptid);
				rss = pstat.executeQuery();

				while (rss.next()) {
					dept.setId(rss.getInt("id"));
					dept.setName(rss.getString("name"));
					// dept.setDept(this.getDeptHeader(rs.getInt(3)));
					if ("T".equals(rss.getString(4))) {
						dept.setStatus(true);
					} else {
						dept.setStatus(false);
					}
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rss);
			}
			return dept;
		}
	}

	public boolean addDept(Dept dept) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "insert into dept values(?,?,?,?)";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, dept.getId());
			pstat.setString(2, dept.getName());
			pstat.setInt(3, dept.getDept().getId());
			if (dept.isStatus()) {
				pstat.setString(4, "T");
			} else {
				pstat.setString(4, "F");
			}
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public boolean deleteDept(Dept dept) {
		// TODO Auto-generated method stub
		return false;
	}

	public boolean deleteDeptById(int deptid) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "delete dept where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, deptid);
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Page<Dept> getAllDept(int pageNo, int pageSize) {
		Page<Dept> page = new Page<Dept>(pageNo, pageSize);
		int totalCount = 0;
		List<Dept> list = new ArrayList<Dept>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept";
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from dept d where rownum<=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
				list.add(dept);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public List<Dept> getALLDept() {
		// TODO Auto-generated method stub
		List<Dept> list = new ArrayList<Dept>();
		conn = dbu.getConnection();
		try {
			pstat = conn.prepareStatement("select * from dept");
			rs = pstat.executeQuery();

			while (rs.next()) {
				Dept dept = new Dept();
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
				list.add(dept);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return list;
	}

	public List<Dept> getDeptByHeaderId(int headerid) {
		// TODO Auto-generated method stub
		return null;
	}

	public Dept getDeptById(int deptid) {
		conn = dbu.getConnection();
		Dept dept = new Dept();
		try {
			pstat = conn.prepareStatement("select * from dept where id=?");
			pstat.setInt(1, deptid);
			rs = pstat.executeQuery();
			while (rs.next()) {
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));

				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return dept;
	}

	public Dept getDeptByName(String name) {
		conn = dbu.getConnection();
		Dept dept = new Dept();
		try {
			pstat = conn.prepareStatement("select * from dept where name='"
					+ name + "'");
			rs = pstat.executeQuery();
			while (rs.next()) {
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return dept;
	}

	public Page<Dept> getDeptByName(int pageNo, int pageSize, String deptname) {
		Page<Dept> page = new Page<Dept>(pageNo, pageSize);
		int totalCount = 0;
		List<Dept> list = new ArrayList<Dept>();
		try {
			conn = dbu.getConnection();
			String sql = "select * from dept t where name=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, deptname);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from dept d where rownum<=? and name=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setString(2, deptname);
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
				list.add(dept);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public int getDeptID(String name) {
		conn = dbu.getConnection();
		int result = -1;
		String sql = "select id from dept where name=?";
		try {
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, name);
			rs = pstat.executeQuery();
			while (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public Dept getHeaderDept(Dept dept) {
		// TODO Auto-generated method stub
		return null;
	}

	public boolean updateDept(Dept dept) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "update dept set name=?,header=?,status=? where id=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(4, dept.getId());
			pstat.setString(1, dept.getName());
			pstat.setInt(2, dept.getDept().getId());
			if (dept.isStatus()) {
				pstat.setString(3, "T");
			} else {
				pstat.setString(3, "F");
			}
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}

	public Page<Dept> getDeptByHeaderId(int pageNo, int pageSize, int headerid) {
		Page<Dept> page = new Page<Dept>(pageNo, pageSize);
		int totalCount = 0;
		List<Dept> list = new ArrayList<Dept>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept where header=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, headerid);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from dept d where rownum<=? and header=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setInt(2, headerid);
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
				list.add(dept);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public Page<Dept> getDeptByStatus(int pageNo, int pageSize, boolean status) {
		Page<Dept> page = new Page<Dept>(pageNo, pageSize);
		int totalCount = 0;
		String statusvalue;
		if (status) {
			statusvalue = "T";
		} else {
			statusvalue = "F";
		}
		List<Dept> list = new ArrayList<Dept>();
		try {
			conn = dbu.getConnection();
			String sql = "select count(*) from dept where status=?";
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, statusvalue);
			rs = pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select d.*,rownum rn from dept d where rownum<=? and status=?) where rn>=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo() * pageSize);
			pstat.setString(2, statusvalue);
			pstat.setInt(3, (page.getPageNo() - 1) * pageSize + 1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Dept dept = new Dept();
				dept.setId(rs.getInt("id"));
				dept.setName(rs.getString("name"));
				Dept dept2 = new Dept();
				dept2 = this.getDeptHeader(rs.getInt("header"));
				dept.setDept(dept2);
				if ("T".equals(rs.getString("status"))) {
					dept.setStatus(true);
				} else {
					dept.setStatus(false);
				}
				list.add(dept);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;
	}

	public boolean getHasDeptEmp(int deptid) {
		// TODO Auto-generated method stub
		return false;
	}
	public boolean updateByHeader(int olddeptid, int newdeptid) {
		boolean flag = false;
		try {
			conn = dbu.getConnection();
			String sql = "update dept set header=? where header=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, newdeptid);
			pstat.setInt(2, olddeptid);
			pstat.executeUpdate();
			flag = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return flag;
	}
}
